#!bin/bash


if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d "-1 days" +%F`
fi


TMP_PRODUCT_SALES_SQL="
DROP TABLE IF EXISTS gd2_tmp_hive.tmp_product_sales;
CREATE TABLE gd2_tmp_hive.tmp_product_sales AS
SELECT
    id,
    product_id,
    source_type,
    stat_date,
    source_uv,
    source_pay_count,
    dt
FROM (
         SELECT
             *,
             ROW_NUMBER() OVER (PARTITION BY product_id, dt ORDER BY id) AS rn
         FROM gd2_tmp_hive.fact_traffic_source
         WHERE dt = '2025-07-30'
     ) sub
WHERE rn = 1;
"

DIM_GL_SJB_SQL="
DROP TABLE IF EXISTS gd2_tmp_hive.dim_gl_sjb;
CREATE TABLE gd2_tmp_hive.dim_gl_sjb AS
SELECT
    p.product_name,
    t.source_type,
    sw.search_word,
    ps.price_strength,
    s.sales_amount,
    t.source_uv,
    s.pay_buyer_count
FROM gd2_tmp_hive.ods_product p
         LEFT JOIN gd2_tmp_hive.tmp_product_sales t
                   ON p.product_id = t.product_id
                       AND p.dt = t.dt
         LEFT JOIN gd2_tmp_hive.fact_search_word sw
                   ON p.product_id = sw.product_id
                       AND p.dt = sw.dt
         LEFT JOIN gd2_tmp_hive.fact_price_strength ps
                   ON p.product_id = ps.product_id
                       AND p.dt = ps.dt
         LEFT JOIN gd2_tmp_hive.fact_product_sales s
                   ON p.product_id = s.product_id
                       AND p.dt = s.dt
WHERE p.dt = '2025-07-30'
AND t.product_id IS NOT NULL
AND sw.product_id IS NOT NULL
AND ps.product_id IS NOT NULL
AND s.product_id IS NOT NULL
ORDER BY s.sales_amount DESC;
"


DIM_PRODUCT_RANKING_SQL="
DROP TABLE IF EXISTS gd2_tmp_hive.dim_product_ranking;
CREATE TABLE gd2_tmp_hive.dim_product_ranking AS
SELECT
    p.product_name AS `商品名称`,
    ps.price_strength AS `价格力等级`,
    s.sales_amount AS `销售额`,
    t.source_uv AS `访客数`,
    s.pay_buyer_count AS `支付买家数`
FROM gd2_tmp_hive.ods_product p
         LEFT JOIN gd2_tmp_hive.tmp_product_sales t
                   ON p.product_id = t.product_id AND p.dt = t.dt
         LEFT JOIN gd2_tmp_hive.fact_search_word sw
                   ON p.product_id = sw.product_id AND p.dt = sw.dt
         LEFT JOIN gd2_tmp_hive.fact_price_strength ps
                   ON p.product_id = ps.product_id AND p.dt = ps.dt
         LEFT JOIN gd2_tmp_hive.fact_product_sales s
                   ON p.product_id = s.product_id AND p.dt = s.dt
WHERE p.dt = '2025-07-30'
  AND t.product_id IS NOT NULL
  AND sw.product_id IS NOT NULL
  AND ps.product_id IS NOT NULL
  AND s.product_id IS NOT NULL
ORDER BY
    CASE ps.price_strength
        WHEN 'excellent' THEN 3
        WHEN 'good' THEN 2
        WHEN 'poor' THEN 1
        ELSE 0
        END DESC,
    s.sales_amount DESC;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -p 123456 -e "${TMP_PRODUCT_SALES_SQL}${DIM_GL_SJB_SQL}${DIM_PRODUCT_RANKING_SQL}"